# -*- coding: utf-8 -*-
"""
Created on Mon Sep 11 09:51:03 2023

@author: tyshixi08
"""

import os
import pandas as pd
import numpy as np
import datetime
import time
from tqdm import tqdm
import Mfactor_get_origin_data as get_origin_data

def data_filter():
    # 提取原始数据
    r = 1  #更新提取，首次则设置为0
    df_valuation_stock_last, df_s, date_n, last_day, date_all = get_origin_data.get_data(r)
    
    # 提取已存储的数据
    df_after_saved = pd.read_pickle(os.path.join('data', 'data_after_filter.pkl'))
    #df_after_saved = df_after_saved[df_after_saved.Date <= '2024-11-22']
    date_last = list(sorted(df_after_saved.Date.unique()))[-1]
    dt_list = list(df_after_saved.datetime.unique())
    print('目前存储数据最新日期{}'.format(dt_list[-1]))
    print('最新数据日期{}'.format(list(date_all)[-1]))
    
    # 只筛选新增数据
    date_all = df_valuation_stock_last["Date"].unique()
    date_all = np.sort(date_all)
    last_day = df_valuation_stock_last["Date"].unique()
    date_n = len(last_day)
    res_n = np.zeros(shape=(date_n - 120, 15))
    res_s = np.zeros(shape=(date_n - 120, 15))
    
    if (pd.to_datetime(date_all[-1]) - pd.to_datetime(date_last)).days == 0:
        print('数据已经更到最新')
    else:
        print('过滤底层券')
        # 新建df_after用于存储过滤后的数据
        df_after1 = pd.DataFrame()
        #for k in tqdm(range(date_n - 120)): # 全量筛选
        for k in tqdm(range(date_n - 120)[-((pd.to_datetime(date_all[-1]) - pd.to_datetime(date_last)).days + 1):]): # 只筛选增量
            temp_data = df_valuation_stock_last[(df_valuation_stock_last['Date'] == last_day[k + 120])]
            dt = temp_data.n_outstandingBalance
        
            # 剔除转债规模小于2亿
            cond1 = (dt < 2)
        
            # 剔除A+及以下
            dt = temp_data.c_issuerRating2
            cond3 = ((dt != "AAA") & (dt != "AA+") & (dt != "AA") & (dt != "AA-"))
        
            # 日均成交额低于100万
            start_20 = date_all[np.where(date_all == last_day[k + 120])[0][0] - 20]
            dt_20 = df_valuation_stock_last[(df_valuation_stock_last['Date'] > start_20) & (df_valuation_stock_last['Date'] <= last_day[k + 120])]
            dt_20 = dt_20[["Date", "c_bondCode", "n_amt", "n_outstandingBalance"]]
            dt_20_res = dt_20.groupby("c_bondCode").mean()
            c = (dt_20_res['n_amt'] < 1000000)
            c = c[c]
            cond4 = (temp_data["c_bondCode"].isin(c.index))
        
            # 上市不足30
            temp_data = temp_data.copy()
            temp_data.t_ipoDate = pd.to_datetime(temp_data.t_ipoDate)
            residual_Day = temp_data.Date - temp_data.t_ipoDate
            cond6 = (residual_Day <= datetime.timedelta(days=30))
            
            # 正股停牌
            dt = temp_data.t_warning
            cond8 = (dt == 1)
            
            # A 股股票的上市公司，连续 120 个交易日通过本所交易系统实现的累计股票成交量低于 500万股
            bond_id = temp_data.c_bondCode.unique()
            start_120 = date_all[np.where(date_all == last_day[k + 120])[0][0] - 120]
            dt_120 = df_s[(df_s['TRADE_DT'] > start_120) & (df_s['TRADE_DT'] <= last_day[k + 120])]
            dt_120 = dt_120[["TRADE_DT", "S_INFO_WINDCODE", "S_DQ_VOLUME"]]
            dt_120_res = dt_120.groupby("S_INFO_WINDCODE").S_DQ_VOLUME.sum()
            c = (dt_120_res < 50000)
            c = c[c]
            cond_120 = (temp_data["c_code"].isin(c.index))
        
            # 连续 20 个交易日的每日股票收盘价均低于 1 元
            start_20 = date_all[np.where(date_all == last_day[k + 120])[0][0] - 20]
            dt_20 = df_valuation_stock_last[(df_valuation_stock_last['Date'] > start_20) & (df_valuation_stock_last['Date'] <= last_day[k + 120])]
            dt_20 = dt_20[["Date", "c_bondCode", "S_DQ_CLOSE", "S_VAL_MV"]]
            dt_20_res = dt_20.groupby("c_bondCode").S_DQ_CLOSE.mean()
            c = (dt_20_res < 1)
            c = c[c]
            cond_20_close = (temp_data["c_bondCode"].isin(c.index))
        
            # 上市公司连续 20 个交易日在本所的每日股票收盘总市值均低于 3 亿元；
            c = (dt_20_res < 3)
            c = c[c]
            cond_20_mv = (temp_data["c_bondCode"].isin(c.index))
        
            # 最近一个会计年度经审计的净利润为负值且营业收入低于 1 亿元，或追溯重述后最近一个会计年度净利润为负值且营业收入低于 1 亿元；
            cond_jlr_yysr = (temp_data["NET_PROFIT_EXCL_MIN_INT_INC"] < 0) & (temp_data["OPER_REV"] < 1E9)
        
            # 最近一个会计年度经审计的期末净资产为负值，或追溯重述后最近一个会计年度期末净资产为负值；
            cond_jzc = (temp_data.TOT_SHRHLDR_EQY_EXCL_MIN_INT < 0)
        
            # 最近一个会计年度的财务会计报告被出具无法表示意见或否定意见的审计报告；
            cond_sjyj = (temp_data.S_STMNOTE_AUDIT_CATEGORY == 405005000) | (temp_data.S_STMNOTE_AUDIT_CATEGORY == 405004000)
        
            bond_id = temp_data.c_bondCode.unique()
            start_30 = date_all[np.where(date_all == last_day[k + 120])[0][0] - 30]
            dt_30 = df_valuation_stock_last[
                (df_valuation_stock_last['Date'] > start_30) & (df_valuation_stock_last['Date'] <= last_day[k + 120])]
        
            dt_30 = dt_30[["Date", "c_bondCode", "n_convValue"]]
            dt_30 = dt_30[dt_30['n_convValue'] >= 130]
            dt_30_res = dt_30.groupby("c_bondCode").n_convValue.count()
            cond_qs = (dt_30_res >= 14)
            code = list(cond_qs[cond_qs == True].index)
            cond_qzsh = (temp_data["c_bondCode"].isin(code))
            n = len(temp_data.c_bondCode.unique())
        
            conv_value = temp_data.n_outstandingBalance
            res_n[k] = np.array([np.sum(cond1), np.sum(cond3), np.sum(cond4), np.sum(cond6), np.sum(cond8), np.sum(cond_120),
                                 np.sum(cond_20_close), np.sum(cond_20_mv), np.sum(cond_jlr_yysr), np.sum(cond_jzc),
                                 np.sum(cond_sjyj), np.sum(cond_120 | cond_20_close | cond_20_mv | cond_jlr_yysr | cond_jzc | cond_sjyj),
                                 np.sum(cond_qzsh), np.sum(cond1 | cond3 | cond4 | cond6 | cond8 | cond_120 | cond_20_close | cond_20_mv | cond_jlr_yysr | cond_jzc | cond_sjyj), n * n]) / n
        
            new_data = temp_data[(cond1 | cond3 | cond4 | cond6 | cond8 | cond_120 | cond_20_close | cond_20_mv | cond_jlr_yysr | cond_jzc | cond_sjyj) == False]
            df_after1 = pd.concat([df_after1, new_data])
        
        condition = ["剔除转债规模小于2亿", "剔除A+及以下", "剔除日均成交额低于100万", "剔除上市不足30日", "剔除正股状态",
                     "连续 120 个交易日通过本所交易系统实现的累计股票成交量低于 500万股", "连续 20 个交易日的每日股票收盘价均低于 1 元",
                     "上市公司连续 20 个交易日在本所的每日股票收盘总市值均低于 3 亿元", "最近一个会计年度经审计的净利润为负值且营业收入低于 1 亿元，或追溯重述后最近一个会计年度净利润为负值且营业收入低于 1 亿元；",
                     "最近一个会计年度经审计的期末净资产为负值，或追溯重述后最近一个会计年度期末净资产为负值；",
                     "最近一个会计年度的财务会计报告被出具无法表示意见或否定意见的审计报告", "退市风险全部条件", "剔除强制赎回", "以上全部条件均剔除", "转债总数"]
        output_condition_n = pd.DataFrame(data=res_n, columns=condition)
        output_condition_n["Date"] = last_day[120:]
        
        # ind = ["Date", "c_bondCode", "n_convValue", "n_convPremiumRatio", "n_amt", "n_outstandingBalance", "n_high", "n_low",
        #        "n_close", "n_pctChange", "n_volume", "n_bondPremiumRatio", "n_implied_vol", 'n_bondValue', 'n_ytmCB',
        #        "S_DQ_HIGH", "S_DQ_LOW", "S_DQ_CLOSE", "S_DQ_PCTCHANGE", "S_VAL_MV", "S_DFA_NETPROFIT_TTM",
        #        "S_DFA_DEDUCTEDPROFIT_TTM", "S_DQ_ADJFACTOR"]
        ind = ['Date', 'c_bondCode', 'c_code', 'n_bondValue', 'n_convPrice', 'n_outstandingBalance', 'n_close']
        df_after1 = df_after1[ind]
        df_after1['Date'] = df_after1['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))
        df_valuation_stock_last = df_valuation_stock_last[ind]
        df_valuation_stock_last['Date'] = df_valuation_stock_last['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))
        
        
        def return_cal(df_slice, field):
            # 计算收益率
            df_slice = df_slice.copy()
            df_slice = df_slice.sort_values(by='datetime', ascending=True)
            df_slice['{}_pctChange_5m'.format(field)] = df_slice['{}_close'.format(field)] / df_slice['{}_close'.format(field)].shift(1) - 1
            
            if field == 'bond': # 假设收盘价买入
                df_slice['tail'] = [datetime.date.strftime(x,'%H:%M:%S') for x in pd.to_datetime(df_slice['datetime'])]
                df_slice_cut = df_slice[df_slice['tail'] != '09:25:00']
                df_slice_cut = df_slice_cut.copy()
                df_slice_cut['pctchange'] = df_slice_cut['{}_close'.format(field)] / df_slice_cut['{}_close'.format(field)].shift(1) - 1
                df_slice_cut['{}_ret_next5m'.format(field)] = df_slice_cut['pctchange'].shift(-1)
                df_slice = df_slice.merge(df_slice_cut[['c_{}Code'.format(field), '{}_ret_next5m'.format(field), 'datetime']], on=['c_{}Code'.format(field), 'datetime'], how='left')
           
            if field == 'bond': # 假设下一分钟买入
                df_slice['tail'] = [datetime.date.strftime(x,'%H:%M:%S') for x in pd.to_datetime(df_slice['datetime'])]
                df_slice_cut = df_slice[df_slice['tail'] != '09:25:00']
                df_slice_cut = df_slice_cut[df_slice_cut['tail'] != '09:31:00']
                df_slice_cut = df_slice_cut.copy()
                df_slice_cut['pctchange'] = df_slice_cut['{}_close_nextmin'.format(field)] / df_slice_cut['{}_close_nextmin'.format(field)].shift(1) - 1
                df_slice_cut['{}_ret_next5m_delay'.format(field)] = df_slice_cut['pctchange'].shift(-1)
                df_slice = df_slice.merge(df_slice_cut[['c_{}Code'.format(field), '{}_ret_next5m_delay'.format(field), 'datetime']], on=['c_{}Code'.format(field), 'datetime'], how='left')
              
            return df_slice
        
        
        print('提取并计算债券5min收益')
        cal_time = time.time()
        bond_data = pd.read_pickle(os.path.join('data', 'bond_data.pkl'))
        bond_data_open = pd.read_pickle(os.path.join('data', 'bond_data_open.pkl'))
        bond_data = pd.concat([bond_data, bond_data_open])
        bond_data['datetime'] = pd.to_datetime(bond_data['datetime'])
        bond_data['Date'] = pd.to_datetime(bond_data['Date'])
        # 为节省计算时间，只计算增量
        bond_data = bond_data[bond_data.datetime >= dt_list[-3]]
        bond_data = bond_data[['Date', 'c_bondCode', 'datetime', 'high', 'low', 'bond_close_nextmin', 'bond_close', 'volume', 'total_turnover']]
        bond_data = bond_data.groupby('c_bondCode').apply(return_cal, 'bond').reset_index(drop=True)
        bond_data = bond_data.rename(columns={'high': 'bond_high', 'low': 'bond_low'})
        # 剔除之前为了计算收益用到的重复时间
        bond_data = bond_data[bond_data.datetime >= dt_list[-2]]
        print('计算用时: {:.2f}s'.format(time.time() - cal_time))
        
        
        print('提取并计算股票5min收益')
        stock_data = pd.read_pickle(os.path.join('data', 'stock_data.pkl'))
        stock_data_open = pd.read_pickle(os.path.join('data', 'stock_data_open.pkl'))
        stock_data = pd.concat([stock_data, stock_data_open])
        stock_data['datetime'] = pd.to_datetime(stock_data['datetime'])
        stock_data['Date'] = pd.to_datetime(stock_data['Date'])
        # 为节省计算时间，只计算增量
        stock_data = stock_data[stock_data.datetime >= dt_list[-3]]
        stock_data = stock_data[['Date', 'c_stockCode', 'datetime', 'high', 'low', 'stock_close']]
        stock_data = stock_data.groupby('c_stockCode').apply(return_cal, 'stock').reset_index(drop=True)
        stock_data = stock_data.rename(columns={'c_stockCode': 'c_code', 'high': 'stock_high', 'low': 'stock_low'})
        # 剔除之前为了计算收益用到的重复时间
        stock_data = stock_data[stock_data.datetime >= dt_list[-2]]
        print('计算用时: {:.2f}s'.format(time.time() - cal_time))
        
        
        print('合并并导出数据')
        # 前一天最后两条涉及未来数据，因此空缺，删除后重新合并
        df_after_saved = df_after_saved[df_after_saved.datetime < dt_list[-2]]
        # 存储筛选后数据
        df_after1['Date'] = pd.to_datetime(df_after1['Date'])
        df_after1 = pd.merge(df_after1, bond_data, on=['c_bondCode', 'Date'], how='inner')
        df_after1 = pd.merge(df_after1, stock_data, on=['c_code', 'Date', 'datetime'], how='left')
        # 存量+增量合并
        df_after1 = pd.concat([df_after_saved, df_after1], axis=0)
        print(len(df_after1))
        df_after1 = df_after1.drop_duplicates(['datetime','c_bondCode'], keep='last')
        print(len(df_after1))
        df_after1 = df_after1.sort_values(by=['datetime','c_bondCode'], ascending=True)
        df_after1.to_pickle(os.path.join('data', 'data_after_filter.pkl'))
        
        # 存储无筛选的全量数据
        data_all_saved = pd.read_pickle(os.path.join('data', 'data_all.pkl'))
        data_all_saved = data_all_saved[data_all_saved.datetime < dt_list[-2]]
        df_valuation_stock_last['Date'] = pd.to_datetime(df_valuation_stock_last['Date'])
        df_valuation_stock_last = pd.merge(df_valuation_stock_last, bond_data, on=['c_bondCode', 'Date'], how='inner')
        df_valuation_stock_last = pd.merge(df_valuation_stock_last, stock_data, on=['c_code', 'Date', 'datetime'], how='left')
        df_valuation_stock_last = pd.concat([data_all_saved, df_valuation_stock_last], axis=0)
        print(len(df_valuation_stock_last))
        df_valuation_stock_last = df_valuation_stock_last.drop_duplicates(['datetime','c_bondCode'])
        print(len(df_valuation_stock_last))
        df_valuation_stock_last = df_valuation_stock_last.sort_values(by=['datetime','c_bondCode'], ascending=True)
        df_valuation_stock_last.to_pickle(os.path.join('data', 'data_all.pkl'))
        print('计算用时: {:.2f}s'.format(time.time() - cal_time))
    return


if __name__ == '__main__':
    data_filter()